/**
 * 
 */
package com.point.business.database.managers;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.point.business.database.model.Schedule;
import com.point.business.database.util.DatabaseConnection;
import com.point.business.database.util.ScheduleThread;

/**
 * @author Ilija Trajkoski
 * 
 */
public class ScheduleManager {
	private static ScheduleManager instance = new ScheduleManager();
	private static Logger logger = Logger.getLogger(ScheduleManager.class);

	private ScheduleManager() {
	}

	public static ScheduleManager getInstance() {
		return instance;
	}

	public boolean addSchedule(int userId, String query, boolean active,
			String objectType, Date searchDate) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		boolean isAdded = false;
		try {
			statement = conn.prepareStatement("insert into schedule"
					+ "(userId, searchQuery, active, objectType, searchDate) "
					+ "values(?,?,?,?,?)");
			statement.setInt(1, userId);
			statement.setString(2, query);
			statement.setBoolean(3, active);
			statement.setString(4, objectType);
			statement.setTimestamp(5, new Timestamp(searchDate.getTime()));
			int result = statement.executeUpdate();
			isAdded = result == 1;
		} catch (SQLException e) {
			logger.error(e);
			isAdded = false;
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return isAdded;
	}

	public boolean disableSchedule(int scheduleId) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		boolean isDisabled = false;
		try {
			statement = conn.prepareStatement("update schedule "
					+ "set active=false where id=?");
			statement.setInt(1, scheduleId);
			int result = statement.executeUpdate();
			isDisabled = result == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return isDisabled;
	}
	
	public boolean deleteSchedules(String scheduleIds) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		boolean isDisabled = false;
		try {
			statement = conn.prepareStatement("delete from schedule where id in("+scheduleIds+")");
			int result = statement.executeUpdate();
			isDisabled = result == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return isDisabled;
	}

	public boolean setSendingTime(int scheduleId) {
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		boolean isDisabled = false;
		try {
			statement = conn.prepareStatement("update schedule "
					+ "set lastAlertSentDate=? where id=?");
			statement.setDate(1,
					new java.sql.Date(new java.util.Date().getTime()));
			statement.setInt(2, scheduleId);
			int result = statement.executeUpdate();
			isDisabled = result == 1;
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return isDisabled;
	}

	public List<Schedule> getAllActiveSchedules() {
		List<Schedule> result = new ArrayList<Schedule>();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("select * from schedule where active=true");
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Schedule schedule = new Schedule();
				schedule.setId(resultSet.getInt("id"));
				schedule.setSearchQuery(resultSet.getString("searchQuery"));
				schedule.setUserId(resultSet.getInt("userId"));
				schedule.setActive(true);
				schedule.setObjectType(resultSet.getString("objectType"));
				if (resultSet.getTimestamp("lastAlertSentDate") != null) {
					schedule.setLastAlertSentDate(new Date(resultSet
							.getTimestamp("lastAlertSentDate").getTime()));
				}
				if (resultSet.getTimestamp("searchDate") != null) {
					schedule.setSearchDate(new Date(resultSet.getTimestamp(
							"searchDate").getTime()));
				}
				result.add(schedule);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public List<Schedule> getAllSchedulesByUser(String type, int userId) {
		List<Schedule> result = new ArrayList<Schedule>();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("select * from schedule where objectType=? AND userId=? order by searchDate desc");
			statement.setString(1, type);
			statement.setInt(2, userId);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Schedule schedule = new Schedule();
				schedule.setId(resultSet.getInt("id"));
				schedule.setSearchQuery(resultSet.getString("searchQuery"));
				schedule.setUserId(resultSet.getInt("userId"));
				schedule.setActive(resultSet.getBoolean("active"));
				schedule.setObjectType(resultSet.getString("objectType"));
				if (resultSet.getTimestamp("lastAlertSentDate") != null) {
					schedule.setLastAlertSentDate(new Date(resultSet
							.getTimestamp("lastAlertSentDate").getTime()));
				}
				if (resultSet.getTimestamp("searchDate") != null) {
					schedule.setSearchDate(new Date(resultSet.getTimestamp(
							"searchDate").getTime()));
				}
				result.add(schedule);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public List<Schedule> getSchedulesForUserForAllTypes(int userId) {
		List<Schedule> result = new ArrayList<Schedule>();
		Connection conn = DatabaseConnection.getInstance().getConnection();
		PreparedStatement statement = null;
		try {
			statement = conn
					.prepareStatement("select * from schedule where userId=? order by searchDate desc");
			statement.setInt(1, userId);
			ResultSet resultSet = statement.executeQuery();
			while (resultSet.next()) {
				Schedule schedule = new Schedule();
				schedule.setId(resultSet.getInt("id"));
				schedule.setSearchQuery(resultSet.getString("searchQuery"));
				schedule.setUserId(resultSet.getInt("userId"));
				schedule.setActive(resultSet.getBoolean("active"));
				schedule.setObjectType(resultSet.getString("objectType"));
				if (resultSet.getTimestamp("lastAlertSentDate") != null) {
					schedule.setLastAlertSentDate(new Date(resultSet
							.getTimestamp("lastAlertSentDate").getTime()));
				}
				if (resultSet.getTimestamp("searchDate") != null) {
					schedule.setSearchDate(new Date(resultSet.getTimestamp(
							"searchDate").getTime()));
				}
				result.add(schedule);
			}
			resultSet.close();
		} catch (SQLException e) {
			logger.error(e);
		} finally {
			DatabaseConnection.getInstance().closeStatement(statement);
		}
		return result;
	}

	public void runCheckNow() {
		ScheduleThread thread = new ScheduleThread();
		thread.start();
	}
}
